package com.kxr.day09.dao;

import com.alibaba.fastjson.JSONObject;
import com.kxr.day09.entity.News;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装了和数据库的交互操作
 */
public class NewsDao {
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2. 创建数据库的连接
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/jingmao20",
                "root",
                "root"
        );
        return conn;
    }

    /**
     * 功能： 判断地址是否存在
     *
     * @param url 请求地址
     * @return 是否存在
     */
    public boolean exist(String url) {
        try {
            Connection conn = getConnection();
            String sql = "select id from news where url = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, url);
            ResultSet resultSet = statement.executeQuery();
            // 拿到结果之后,获取一下下一项，如果这这一项存在，代表这个数据已经在数据库里存在
            // 如果不存在，代表我们什么都没有获取到
            boolean hasNext = resultSet.next();
            resultSet.close();
            statement.close();
            conn.close();
            return hasNext;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 实现数据库的保存方法
     *
     * @param entity 传递过来的实体类
     * @return 影响的行数
     */
    public boolean save(News entity) {
        try {
            Connection conn = getConnection();
            // 查询操作
            // todo 定义sql模板
            String sql = "insert into news(url, title, time, content, content_html, category, source, author) " +
                    "value (?,?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            // todo 设置参数
            int i = 1;
            preparedStatement.setString(i++, entity.getUrl());
            preparedStatement.setString(i++, entity.getTitle());
            // java.sql.Date只有年月日
            // java.sql.Time只有时分秒
            // java.sql.Timestamp 包含年月日和时分秒
            // java.util.Date 转成 java.sql.Date
            preparedStatement.setTimestamp(i++, entity.getTime());
            preparedStatement.setString(i++, entity.getContent());
            preparedStatement.setString(i++, entity.getContentHtml());
            preparedStatement.setString(i++, entity.getCategory());
            preparedStatement.setString(i++, entity.getSource());
            preparedStatement.setString(i++, entity.getAuthor());
            // todo 执行sql  execute 和 executeQuery（执行查询操作之后得到resultSet，通过resultSet拿到查询结果）
            preparedStatement.execute();
            preparedStatement.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    /**
     * 根据关键字检索到新闻
     * @param keywords 关键子
     * @return 新闻列表，只要10条
     */
    public List<News> search(String keywords) {
        List<News> list = new ArrayList<>();
        try {
            Connection conn = getConnection();
            String sql = "select * from news where title like ? limit 20";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1,"%"+keywords+"%");
            ResultSet resultSet= statement.executeQuery();

            while (resultSet.next()) {
                // 获取参数设置到news上，然后添加到列表里
                News news = new News();
                news.setId(resultSet.getInt("id"));
                news.setTitle(resultSet.getString("title"));
                news.setSource(resultSet.getString("source"));
                news.setCategory(resultSet.getString("category"));
                news.setAuthor(resultSet.getString("author"));
                news.setContent(resultSet.getString("content"));
                news.setTime(resultSet.getTimestamp("time"));
                news.setUrl(resultSet.getString("url"));
                news.setContentHtml(resultSet.getString("content_html"));
                // 放入列表
                list.add(news);
            }

            statement.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<JSONObject> sourceCount() {
        List<JSONObject> list = new ArrayList<>();
        try {
            Connection conn = getConnection();
            String sql = "select source ,count(1) as num from news group by source";
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet= statement.executeQuery();

            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("name", resultSet.getString("source"));
                jsonObject.put("value", resultSet.getInt("num"));
                list.add(jsonObject);
            }

            statement.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<JSONObject> timelineNewsNum() {
        List<JSONObject> list = new ArrayList<>();
        try {
            Connection conn = getConnection();
            String sql = "select date, count(1) as num\n" +
                    "from (\n" +
                    "         select DATE_FORMAT(time, '%Y-%m-%d') as date\n" +
                    "         from news\n" +
                    "     ) date_tbl\n" +
                    "group by date\n" +
                    "order by date desc\n" +
                    "limit 7";
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet= statement.executeQuery();

            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("name", resultSet.getString("date"));
                jsonObject.put("value", resultSet.getInt("num"));
                list.add(jsonObject);
            }

            statement.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
